﻿using Microsoft.Reporting.WebForms;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WeddingEventManagementApp.Reports
{
    public partial class EventReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SetupReportParams();
                SetupReport();
            }
        }

        private void RefreshReport(LocalReport localReport)
        {
            DateTime startDate = DateTime.Parse(txtStartDate.Text);
            DateTime endDate = DateTime.Parse(txtEndDate.Text);

            string clientName;
            if (drpClientName.SelectedItem == null || drpClientName.SelectedItem.Value == "All Clients")
                clientName = null;
            else
                clientName = drpClientName.SelectedItem.Value;

            string eventType;
            if (drpEventType.SelectedItem == null || drpEventType.SelectedItem.Value == "All Event Types")
                eventType = null;
            else
                eventType = drpEventType.SelectedItem.Value;

            string packageType;
            if (drpPackageType.SelectedItem == null || drpPackageType.SelectedItem.Value == "All Packages")
                packageType = null;
            else
                packageType = drpPackageType.SelectedItem.Value;

            DataTable dt1 = new DataTable("DataSet1");
            GetDataSet1(eventType, packageType, clientName, startDate, endDate, ref dt1);

            ReportDataSource rds1 = new ReportDataSource();
            rds1.Name = "DataSet1";
            rds1.Value = dt1;
            localReport.DataSources.Add(rds1);

            DataTable dt2 = new DataTable("EventCountPackageType");
            GetEventCountPackageType(startDate, endDate, eventType, packageType, clientName, ref dt2);
            
            ReportDataSource rds2 = new ReportDataSource();
            rds2.Name = "EventCountPackageType";
            rds2.Value = dt2;
            localReport.DataSources.Add(rds2);

            DataTable dt3 = new DataTable("EventCountEventType");
            GetEventCountEventType(startDate, endDate, eventType, packageType, clientName, ref dt3);

            ReportDataSource rds3 = new ReportDataSource();
            rds3.Name = "EventCountEventType";
            rds3.Value = dt3;
            localReport.DataSources.Add(rds3);

            localReport.Refresh();
        }

        protected void btnViewReport_Click(object sender, EventArgs e)
        {
            LocalReport localReport = ReportViewer1.LocalReport;
            localReport.DataSources.Clear();
            RefreshReport(localReport);
        }

        #region Setup Report

        private void SetupReport()
        {
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.ShowParameterPrompts = true;

            LocalReport localReport = ReportViewer1.LocalReport;
            localReport.ReportPath = "Reports/Events.rdlc";

            ReportParameter eventParam = new ReportParameter();
            eventParam.Name = "EventType";
            eventParam.Values.Add(" ");
            eventParam.Visible = true;

            ReportParameter packageParam = new ReportParameter();
            packageParam.Name = "PackageType";
            packageParam.Values.Add(" ");
            packageParam.Visible = true;

            ReportParameter namesParam = new ReportParameter();
            namesParam.Name = "ClientName";
            namesParam.Values.Add(" ");
            namesParam.Visible = true;

            localReport.SetParameters(new ReportParameter[] { eventParam, packageParam, namesParam });

            RefreshReport(localReport);
        }

        #endregion

        #region Setup Params

        private void SetupReportParams()
        {
            txtStartDate.Text = GetDefaultDate("start").ToShortDateString();
            txtEndDate.Text = GetDefaultDate("end").ToShortDateString();

            //client names
            DataTable clientNames = new DataTable("ClientNames");
            GetClientNames(ref clientNames);
            DataRow newClientRow = clientNames.NewRow();
            newClientRow["Client Name"] = "All Clients";
            clientNames.Rows.InsertAt(newClientRow, 0);

            drpClientName.DataSource = clientNames;
            drpClientName.DataTextField = "Client Name";
            drpClientName.DataValueField = "Client Name";
            drpClientName.DataBind();

            //package types
            DataTable packageTypes = new DataTable("PackageTypes");
            GetPackageTypes(ref packageTypes);
            DataRow newPackageTypeRow = packageTypes.NewRow();
            newPackageTypeRow["PackageType"] = "All Packages";
            packageTypes.Rows.InsertAt(newPackageTypeRow, 0);

            drpPackageType.DataSource = packageTypes;
            drpPackageType.DataTextField = "PackageType";
            drpPackageType.DataValueField = "PackageType";
            drpPackageType.DataBind();

            //event types
            DataTable eventTypes = new DataTable("EventTypes");
            GetEventTypes(ref eventTypes);
            DataRow newEventTypeRow = eventTypes.NewRow();
            newEventTypeRow["EventTypeName"] = "All Event Types";
            eventTypes.Rows.InsertAt(newEventTypeRow, 0);

            drpEventType.DataSource = eventTypes;
            drpEventType.DataTextField = "EventTypeName";
            drpEventType.DataValueField = "EventTypeName";
            drpEventType.DataBind();
        }

        private void GetEventTypes(ref DataTable eventTypes)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter("select distinct EventTypeName from EventType", con);
                adp.Fill(eventTypes);
            }
        }

        private void GetPackageTypes(ref DataTable packageTypes)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter("select distinct PackageType from Package", con);
                adp.Fill(packageTypes);
            }
        }

        private void GetClientNames(ref DataTable dataTable)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("sp_getClientNames", con);
                adapter.Fill(dataTable);
            }
        }

        private DateTime GetDefaultDate(string startEnd)
        {
            DateTime currentDate = DateTime.Now;
            if (startEnd == "start")
            {
                return new DateTime(currentDate.Year, 01, 01);
            }
            else if (startEnd == "end")
            {
                return new DateTime(currentDate.Year, 12, 31);
            }
            return DateTime.Now;
        }

        #endregion

        #region DataSets

        private void GetDataSet1(string eventType, string packageType, string clientName, DateTime startDate, 
            DateTime endDate, ref DataTable dataTable)
        {
            using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter("pr_GetEventsReportData @EventType, @PackageType, @ClientName, @DateRangeStart, @DateRangeEnd",
                    con);

                if (eventType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@EventType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@EventType", eventType);

                if (packageType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@PackageType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@PackageType", packageType);

                if (clientName == null)
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", clientName);
                
                adp.SelectCommand.Parameters.AddWithValue("@DateRangeStart", startDate);
                adp.SelectCommand.Parameters.AddWithValue("@DateRangeEnd", endDate);

                adp.Fill(dataTable);
            }
        }

        private void GetEventCountPackageType(DateTime startDate , DateTime endDate, string eventType, 
            string packageType, string clientName,  ref DataTable dataTable)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter("pr_GetEventCountPackageType @DateRangeStart, @DateRangeEnd, @EventType, @PackageType, @ClientName", 
                    con);
                if (eventType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@EventType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@EventType", eventType);

                if (packageType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@PackageType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@PackageType", packageType);

                if (clientName == null)
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", clientName);

                adp.SelectCommand.Parameters.AddWithValue("@DateRangeStart", startDate);
                adp.SelectCommand.Parameters.AddWithValue("@DateRangeEnd", endDate);

                adp.Fill(dataTable);
            }
        }

        private void GetEventCountEventType(DateTime startDate, DateTime endDate, string eventType,
            string packageType, string clientName, ref DataTable dataTable)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter("pr_GetEventCountEventType @DateRangeStart, @DateRangeEnd, @EventType, @PackageType, @ClientName",
                    con);
                if (eventType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@EventType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@EventType", eventType);

                if (packageType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@PackageType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@PackageType", packageType);

                if (clientName == null)
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", clientName);

                adp.SelectCommand.Parameters.AddWithValue("@DateRangeStart", startDate);
                adp.SelectCommand.Parameters.AddWithValue("@DateRangeEnd", endDate);

                adp.Fill(dataTable);
            }
        }

        #endregion

    }
}